/**
 * 
 */
package dao;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import model.MonHocBatBuocM;
import model.MonHocM;
import model.NhomMonHocM;
import util.DAL;
import util.MyConnectUnit;

public class MonHocBatBuocD {
	private MyConnectUnit conn;

	/**
	 * @function constructor MonHocBatBuocD 
	 * @throws Exception 
	 */
	public MonHocBatBuocD() throws Exception {
		super();
		this.conn = DAL.getDAL();
	}
	
	/**
	 * @function get all MonHocBatBuoc
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getAlls(String ten, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<MonHocBatBuocM> ls = new ArrayList<MonHocBatBuocM>();
		ResultSet rs = this.conn.Selects(
						"mhbb.id_mon_hoc, mh.ten, mhbb.id_nhom_mon_hoc, mnh.ten",
						"MON_HOC_BAT_BUOC mhbb, MON_HOC mh, NHOM_MON_HOC mnh",
						"mhbb.id_mon_hoc = mh.id_mon_hoc AND mhbb.id_nhom_mon_hoc = mnh.id_nhom_mon_hoc AND mh.ten LIKE '%" + ten + "'",
						"mhbb.id_mon_hoc ASC",
						limit);
		MonHocBatBuocM l = null;
		while (rs.next()) {
			l = new MonHocBatBuocM();
			
			l.setId_mon_hoc(rs.getInt("mhbb.id_mon_hoc"));
			MonHocM mh = new MonHocM();
			mh.setTen(rs.getString("mh.ten"));
			l.setTen_mon_hoc(mh);
			
			l.setId_nhom_mon_hoc(rs.getInt("mhbb.id_nhom_mon_hoc"));
			NhomMonHocM nmh = new NhomMonHocM();
			nmh.setTen(rs.getString("mnh.ten"));
			l.setTen_nhom_mon_hoc(nmh);
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all MonHocBatBuoc
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getDetail(int id) throws Exception {
		ArrayList<MonHocBatBuocM> ls = new ArrayList<MonHocBatBuocM>();
		ResultSet rs = this.conn.Selects(
						"bb.id_nhom_mon_hoc, bb.id_mon_hoc, nmh.ten, mh.ma, mh.ten, mh.so_tc, mh.so_tiet_lt, mh.so_tiet_th",
						"NHOM_MON_HOC nmh, MON_HOC_BAT_BUOC bb, MON_HOC mh",
						"bb.id_nhom_mon_hoc = nmh.id_nhom_mon_hoc AND bb.id_mon_hoc = mh.id_mon_hoc AND nmh.id_khoi_kien_thuc = " + id,
						"mh.ma ASC",
						null);
		MonHocBatBuocM l = null;
		while (rs.next()) {
			l = new MonHocBatBuocM();
			
			l.setId_nhom_mon_hoc(rs.getInt("bb.id_nhom_mon_hoc"));
			NhomMonHocM nmh = new NhomMonHocM();
			nmh.setTen(rs.getString("nmh.ten"));
			l.setTen_nhom_mon_hoc(nmh);
			
			l.setId_mon_hoc(rs.getInt("bb.id_mon_hoc"));
			MonHocM mh = new MonHocM();
			mh.setMa(rs.getString("mh.ma"));
			mh.setTen(rs.getString("mh.ten"));
			mh.setSo_tc(rs.getInt("mh.so_tc"));
			mh.setSo_tiet_lt(rs.getString("mh.so_tiet_lt"));
			mh.setSo_tiet_th(rs.getInt("mh.so_tiet_th"));
			
			l.setMa_mon_hoc(mh);
			l.setTen_mon_hoc(mh);
			l.setSo_tc(mh);
			l.setSo_tiet_lt(mh);
			l.setSo_tiet_th(mh);
			
			ls.add(l);
		}
		return ls;
	}
	
	
	/**
	 * @function get all MonHocBatBuoc
	 * @param id
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getDetail1(int id) throws Exception {
		ArrayList<MonHocBatBuocM> ls = new ArrayList<MonHocBatBuocM>();
		ResultSet rs = this.conn.Selects(
						"nmh.id_nhom_mon_hoc, mh.id_mon_hoc, nmh.ten, mh.ma, mh.ten, mh.so_tc, mh.so_tiet_lt, mh.so_tiet_th",
						"NHOM_MON_HOC nmh, MON_HOC mh, NHOM_MON_TU_CHON nmtc, MON_HOC_NHOM_TU_CHON mhntc",
						"AND nmh.id_nhom_mon_hoc = nmtc.id_nhom_mon_hoc AND nmtc.id_nhom_mon_tu_chon = mhntc.id_nhom_tu_chon AND mhntc.id_mon_hoc = mh.id_mon_hoc " +
						"AND nmh.id_khoi_kien_thuc = " + id,
						"mh.ma ASC",
						null);
		MonHocBatBuocM l = null;
		while (rs.next()) {
			l = new MonHocBatBuocM();
			
			l.setId_nhom_mon_hoc(rs.getInt("nmh.id_nhom_mon_hoc"));
			NhomMonHocM nmh = new NhomMonHocM();
			nmh.setTen(rs.getString("nmh.ten"));
			l.setTen_nhom_mon_hoc(nmh);
			
			l.setId_mon_hoc(rs.getInt("mh.id_mon_hoc"));
			MonHocM mh = new MonHocM();
			mh.setMa(rs.getString("mh.ma"));
			mh.setTen(rs.getString("mh.ten"));
			mh.setSo_tc(rs.getInt("mh.so_tc"));
			mh.setSo_tiet_lt(rs.getString("mh.so_tiet_lt"));
			mh.setSo_tiet_th(rs.getInt("mh.so_tiet_th"));
			
			l.setMa_mon_hoc(mh);
			l.setTen_mon_hoc(mh);
			l.setSo_tc(mh);
			l.setSo_tiet_lt(mh);
			l.setSo_tiet_th(mh);
			
			ls.add(l);
		}
		return ls;
	}
	
	/**
	 * @function get all MonHocBatBuoc
	 * @param Condition
	 * @param Limit
	 * @param OrderBy
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getAll(String Condition, String Limit, String OrderBy) throws Exception {
		ArrayList<MonHocBatBuocM> ls = new ArrayList<MonHocBatBuocM>();
		ResultSet rs = this.conn.Select("MON_HOC_BAT_BUOC", Condition, Limit, OrderBy);
		MonHocBatBuocM l = null;
		while (rs.next()) {
			l = new MonHocBatBuocM();
			l.setId_mon_hoc(rs.getInt("id_mon_hoc"));
			l.setId_nhom_mon_hoc(rs.getInt("id_nhom_mon_hoc"));
			ls.add(l);
		}
		return ls;
	}

	/**
	 * @function get all MonHocBatBuoc with Condition + Limit
	 * @param Condition
	 * @param Limit
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getAll(String Condition, String Limit) throws Exception {
		return getAll(Condition, Limit, null);
	}

	/**
	 * @function get all MonHocBatBuoc with Condition
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getAll(String Condition) throws Exception {
		return getAll(Condition, null);
	}
	
	/**
	 * @function get all MonHocBatBuoc no parameter
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getAll() throws Exception {
		return getAll(null);
	}
	
	/**
	 * @function search ten MonHocBatBuoc
	 * @param ten
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> searchMHTQ(int id) throws Exception {
		ArrayList<MonHocBatBuocM> list = getAll("id_mon_hoc = " + id);
		return list;
	}
	
	/**
	 * @function check exists MonHocBatBuoc
	 * @param id_mon_hoc
	 * @param id_nhom_mon_hoc
	 * @return
	 * @throws Exception
	 */
	public int checkExists(int id_mon_hoc, int id_nhom_mon_hoc) throws Exception {
		ArrayList<MonHocBatBuocM> list = getAll("id_mon_hoc = " + id_mon_hoc + " AND id_nhom_mon_hoc = " + id_nhom_mon_hoc);
		return list.size();
	}
	
	/**
	 * @function get all MonHocBatBuoc with search + paging
	 * @param from
	 * @param to
	 * @return
	 * @throws Exception
	 */
	public ArrayList<MonHocBatBuocM> getLimit(int id, int from, int to) throws Exception {
		String limit = from + ", " + to;
		ArrayList<MonHocBatBuocM> list = getAll("id_mon_hoc = " + id, limit);
		return list;
	}

	/**
	 * function insert MonHocBatBuoc
	 * @param mhbb
	 * @return
	 * @throws Exception
	 */
	public boolean Insert(MonHocBatBuocM mhbb) throws Exception {
		HashMap<String, Object> map = new HashMap<String, Object>();
		map.put("id_mon_hoc", mhbb.getId_mon_hoc());
		map.put("id_nhom_mon_hoc", mhbb.getId_nhom_mon_hoc());
		return this.conn.Insert("MON_HOC_BAT_BUOC", map);
	}

	/**
	 * @function delete MonHocBatBuoc
	 * @param id_mon_hoc
	 * @param id_nhom_mon_hoc
	 * @return
	 * @throws Exception
	 */
	public boolean Delete(int id_mon_hoc, int id_nhom_mon_hoc) throws Exception {
		return this.conn.Delete("MON_HOC_BAT_BUOC", "id_mon_hoc = " + id_mon_hoc + " AND id_nhom_mon_hoc = " + id_nhom_mon_hoc);
	}
	
	public static void main(String[] args) throws Exception{
		MonHocBatBuocD dao = new MonHocBatBuocD();
		ArrayList<MonHocBatBuocM> ls = dao.getDetail1(14);
		System.out.println(ls.size());
	}

}
